package com.dragon.common.util;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.Arrays;
import java.util.List;

public class ExportUtil {


    public static void exportExcel(List<List<Object>> row, HttpServletResponse response,String name)throws UnsupportedEncodingException {
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.setColumnWidth(-1,30);
        writer.setRowHeight(-1,25);
        Font font = writer.createFont();
        font.setBold(true);
        writer.write(row, true);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=test.xls");
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        writer.flush(out, true);
        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }
    /**
     * Excel导出设置Workbook
     * @param title 导出Excel文件名称
     * @param rowList 第一个List为表头，其余行为表数据
     * @param downLoadPic 是否下载图片
     * @throws IOException
     */
    public static HSSFWorkbook warpSingleWorkbook(String title,List<List<Object>> rowList, Boolean downLoadPic) throws IOException {
        if (rowList == null || rowList.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        HSSFWorkbook book = new HSSFWorkbook();
        // 创建表
        HSSFSheet sheet = book.createSheet(title);
        // 设置单元格默认宽度为20个字符
        sheet.setDefaultColumnWidth(20);
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 设置表头样式
        HSSFCellStyle style = book.createCellStyle();
        //设置字体
        HSSFFont font = book.createFont();
        //font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        font.setBold(false);//粗体显示
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);

        // 检测表头数据（表头不允许数据为空）
        List<Object> head = rowList.get(0);
        // 写数据
        int size = rowList.get(0).size();
        // 第几行
        for (int i = 0; i < rowList.size(); i++) {
            List<Object> row = rowList.get(i);
            if (row == null || row.isEmpty()) {
                book.close();
                throw new NullPointerException("the "+(i+1)+"th row is null");
            }
            if (i < 2){
                if (size != row.size()) {
                    book.close();
                    throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
                }
            }
            HSSFRow sr = sheet.createRow(i);
            // 第几列
            for (int j = 0; j < row.size(); j++) {
                // 如果是否下载照片选择了true，由于我们的照片放在第三列，即当循环到序列为2的时候获取到网络图片地址，这里还考虑到了有多张照片的情况，且用逗号拼接成多张照片。
                if (downLoadPic && i > 2 && j == 0) {
                    if (StringUtils.isNotBlank(row.get(0).toString())) {
                        sr.setHeight((short) (1800));
                        List<String> images = Arrays.asList(row.get(0).toString().split(","));
                        int temp = j;
                        for (String image : images) {
                            // 调用封装好的下载图片方法
                            drawPictureInfoExcel(book, patriarch, i, j, image);
                            j++;
                        }
                    }
                } else {
                    setExcelValue(sr.createCell(j), row.get(j), style);
                }
            }
        }
        return book;
    }

    /**
     * 设置Excel浮点数可做金额等数据统计
     * @param cell 单元格类
     * @param value 传入的值
     */
    public static void setExcelValue(HSSFCell cell, Object value, HSSFCellStyle style){
        // 写数据
        if (value == null) {
            cell.setCellValue("");
        }else {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellValue(Long.valueOf(value.toString()));
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal)value).setScale(3, RoundingMode.HALF_UP).doubleValue());
            } else {
                cell.setCellValue(value.toString());
            }
            cell.setCellStyle(style);
        }
    }

    /**
     *
     * @param wb
     * @param patriarch
     * @param rowIndex
     * @param columnIndex
     * @param pictureUrl
     */
    public static void drawPictureInfoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, int rowIndex, int columnIndex, String pictureUrl){
        //rowIndex代表当前行
        try {
            URL url = new URL(pictureUrl);
            //打开链接
            HttpURLConnection conn = (HttpURLConnection)url.openConnection();
            //设置请求方式为"GET"
            conn.setRequestMethod("GET");
            //超时响应时间为5秒
            conn.setConnectTimeout(5 * 1000);
            //通过输入流获取图片数据
            InputStream inStream = conn.getInputStream();
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性
            byte[] data = readInputStream(inStream);
            //anchor主要用于设置图片的属性
            // dx1：起始单元格的x偏移量，
            // dy1：起始单元格的y偏移量，
            // dx2：终止单元格的x偏移量，
            // dy2：终止单元格的y偏移量，
            // col1：起始单元格列序号，从0开始计算；
            // row1：起始单元格行序号，从0开始计算，
            // col2：终止单元格列序号，从0开始计算；
            // row2：终止单元格行序号，从0开始计算，
            HSSFClientAnchor anchor = new HSSFClientAnchor(30, 0, 1003, 255,(short) columnIndex, rowIndex, (short) columnIndex, rowIndex);
            //Sets the anchor type （图片在单元格的位置）
            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while( (len=inStream.read(buffer)) != -1 ){
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }
}
